package com.mycompany.inventory.persistence;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.pojo.Address;
import com.mycompany.inventory.pojo.ProductInventory;

import java.util.ArrayList;
import java.util.List;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by My Company.
 * User: loitran
 * Date: Feb 29, 2012
 * Time: 1:53:19 PM
 * com.mycompany.inventory.persistence.
 */
public class ProductInventoryDAO extends AbstractDao<ProductInventory>{
	
	private Connection con = this.connect;
	public static final String SELECT_ALL_SQL = "select * from oce_product_inventory";
	public static final String SELECT_SQL = "select * from oce_product_inventory where productInventoryId = ?";
	public static final String DELETE_SQL = "DELETE FROM oce_product_inventory WHERE product_id = ? and inventory_id = ?";
	public static final String UPDATE_QUANTITY_SQL = "select * FROM oce_product_inventory WHERE product_id = ? and inventory_id = ?";
	public static final String SELECT_BY_PRODUCTID_SQL = "select * from oce_product_inventory where product_id = ?";
	public static final String SELECT_BY_INVID_SQL = "select * from oce_product_inventory where inventory_id = ?";
	public static final String INSERT_SQL = "INSERT INTO oce_product_inventory (productInventoryId,product_id,inventory_id,quantity,isdefault) VALUES (?,?,?,?,?)";
	public static final String UPDATE_SQL = "UPDATE oce_product_inventory SET quantity = ? WHERE product_id = ? and inventory_id = ?";

	
	public ProductInventory create(ProductInventory obj){
		try{
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = 0l;
			ps.setLong(1,id);
			ps.setLong(2, Long.parseLong(obj.getProductId()));
			ps.setLong(3, Long.parseLong(obj.getInventoryId()));
			ps.setLong(4, obj.getQuantity());
			ps.setString(5, obj.getIsdefault());
			ConnectionDatabase.executeUpdate(ps);	
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
			ps.close();			
			
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}
	
	public ProductInventory update(ProductInventory obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = 0l;
			ps.setLong(1, obj.getQuantity());
			ps.setLong(2, Long.parseLong(obj.getProductId()));
			ps.setLong(3, Long.parseLong(obj.getInventoryId()));			
			ConnectionDatabase.executeUpdate(ps);	
		}catch (Exception e) {
			// TODO: handle exception
		}
		return obj;
	}
	
	public ProductInventory find(long id){
		List<ProductInventory> pInventory = new ArrayList<ProductInventory>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			pInventory = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
		}
		return pInventory.size()== 0 ? null : pInventory.get(0);
	}
	
	public ProductInventory findInventory(long id, long invId){
		List<ProductInventory> pInventory = new ArrayList<ProductInventory>();
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_QUANTITY_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			ps.setLong(2, invId);
			pInventory = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
		}
		return pInventory.size()== 0 ? null : pInventory.get(0);
	}	
	
	public List<ProductInventory> findByProductID(long id){
		List<ProductInventory> pInves = new ArrayList<ProductInventory>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_BY_PRODUCTID_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			pInves = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			pInves = null;
		}
		return pInves;
	}	
	
	public List<ProductInventory> findByInvID(long id){
		List<ProductInventory> pInves = new ArrayList<ProductInventory>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_BY_INVID_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			pInves = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
			pInves = null;
		}
		return pInves;
	}	
	
	public List<ProductInventory> findAll(){
		List<ProductInventory> pInventory = new ArrayList<ProductInventory>();
		try{
			pInventory = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			pInventory = null;
		}
		return pInventory;
	}
	
	public void delete(ProductInventory obj){
		try{
			PreparedStatement ps = con.prepareStatement(DELETE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,Long.parseLong(obj.getProductId()));
			ps.setLong(2,Long.parseLong(obj.getInventoryId()));
			ConnectionDatabase.executeUpdate(ps);	
			ps.close();	
		}catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	private List<ProductInventory> convert(ResultSet rs) throws SQLException {
		List<ProductInventory> pInventory = new ArrayList<ProductInventory>();
		while (rs.next()) {
	        // Get the data from the row using the column index
	        String id = rs.getString(1);
	        String productId = rs.getString(2);
	        String inventoryId = rs.getString(3);
	        int quantity = rs.getInt(4);
	        String isDefault = rs.getString(5);
	        pInventory.add(new ProductInventory(id,productId,inventoryId,quantity,isDefault));
	    }
		rs.close();
		return pInventory;
	}
}
